package fr.gdi.android.news;

import java.text.SimpleDateFormat;
import java.util.List;
import java.util.TimeZone;
import java.util.concurrent.locks.ReentrantLock;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import fr.gdi.android.news.utils.Constants.Provider;
import fr.gdi.android.news.utils.IOUtils;

public class DatabaseHelper extends SQLiteOpenHelper
{
    public static SimpleDateFormat SQL_DATE_FORMAT = new SimpleDateFormat("MMM d, HH:mm");
    public static SimpleDateFormat SQL_DATE_FORMAT_WITH_YEAR = new SimpleDateFormat("yyyy-MM-dd, HH:mm");
    
    public static final String NEWS_TABLE = "news";
    public static final String COLOR_TABLE = "colors";
    
    private static final String DATABASE_NAME = "news.db";
    private static final int DATABASE_VERSION = 4;
    
    private static ReentrantLock lock = new ReentrantLock();
    
    static
    {
        SQL_DATE_FORMAT.setTimeZone(TimeZone.getDefault());
    }
    
    public static class Query
    {
        public String sql;
        public String[] params;
        
        public Query(String query, String[] params)
        {
            this.sql = query;
            this.params = params;
        }
    }
    
    public DatabaseHelper(Context context)
    {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }
    
    @Override
    public synchronized SQLiteDatabase getReadableDatabase()
    {
        SQLiteDatabase db = super.getReadableDatabase();
        db.setLockingEnabled(false);
        return db;
    }
    
    @Override
    public synchronized SQLiteDatabase getWritableDatabase()
    {
        SQLiteDatabase db = super.getWritableDatabase();
        db.setLockingEnabled(false);
        return db;
    }
    
    @Override
    public void onCreate(SQLiteDatabase db)
    {
        String feedName = Provider.Columns.FEED_NAME.toString();
        db.execSQL("create table if not exists " + COLOR_TABLE + " (" +  feedName + " primary key, color integer)");
        db.execSQL("create table if not exists " + NEWS_TABLE + " (" + "oid integer primary key autoincrement, " + feedName
                + " VARCHAR(50) references colors(" + feedName + "), " + Provider.Columns.NEWS_AUTHOR.toString() + " VARCHAR(255), " 
                + Provider.Columns.NEWS_DATE.toString() + " DATE, "
                + Provider.Columns.NEWS_DESCRIPTION.toString() + " TEXT, " + Provider.Columns.NEWS_LINK + " TEXT, " + Provider.Columns.NEWS_TITLE + " TEXT,"
                + Provider.Columns.NEWS_IMAGE_URL + " TEXT);");
    }
    
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
    {
        IOUtils.relocateExternalStorage();
        db.execSQL("drop table if exists " + NEWS_TABLE);
        onCreate(db);
    }
    
    public Query getDeleteQuery(List<String> names)
    {
        StringBuilder builder = new StringBuilder("delete from ").append(NEWS_TABLE).append(" where ").append(Provider.Columns.FEED_NAME.toString()).append(
                " in (");
        
        String[] params = new String[names.size()];
        for (int u = 0, l = names.size(); u < l; u++)
        {
            String s = names.get(u);
            if (u > 0) builder.append(", ");
            builder.append("?");
            params[u] = s;
        }
        
        return new Query(builder.append(")").toString(), params);
    }
    
    public static void lock()
    {
        lock.lock();
    }
    
    public static void unlock()
    {
        lock.unlock();
    }
}
